<?php

Zend_Loader::loadClass("BaseDBRows");
Zend_Loader::loadClass("ReceiptReject");
class ReceiptRejects extends BaseDBRows
{
	protected function setUp()
	{
		$this->_tableName = ReceiptReject::_tablename;
	}	
	
	public function getMonthlyListOfRejectSummary($orgType, $country="", $province="", $orgId=0, $dateFrom="", $dateTo="") {
		
		Zend_Loader::loadClass("Receipt");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("OrganizationAddress");
		Zend_Loader::loadClass("Address");
		Zend_Loader::loadClass("QueryCreator");

		$query = new QueryCreator();
		$query->addSelect("date_format(r.date_receipt,'%Y%m') yearmonth, cast(100*(sum(rr.reject_quantity)/sum(r.received_quantity)) as unsigned) reject_percentage");
		$query->addFrom(Receipt::_tablename . " r ");
		$query->addJoin(", " . $this->_tableName . " rr ");
		$query->addJoin(", " . Organization::_tablename . " org ");
		$query->addJoin(" left join " . OrganizationAddress::_tablename . " oa on org.primary_address_id = oa.org_address_id and org.org_id = oa.org_id");
		$query->addJoin(" left join " . Address::_tablename . " a on oa.address_id = a.address_id");
		$query->addWhere("r.receipt_id = rr.receipt_id");
		$query->addWhere("r.org_id = rr.org_id");
		$query->addWhere("r.supplier_org_id = org.org_id");
		$query->addWhere("org.org_type = '" . $orgType . "'");
		$query->addGroupBy("date_format(r.date_receipt,'%Y%m')");

		// country
		if(strlen($country) > 0) {
			$query->addWhere("a.country_code = " . $country);
		}
		
		// province
		if(strlen($province) > 0) {
			$query->addWhere("a.province = " . $province);
		}
		
		// org 
		if(strlen($orgId) > 0 && $orgId > 0) {
			$query->addWhere("org.org_id = " . $orgId);
		}

		// dates from - to
		if(strlen($dateFrom) > 0 && strlen($dateTo) > 0) {
			$query->addWhere(" r.date_receipt between least('" . $dateFrom . "','" . $dateTo . "') and greatest('" . $dateFrom . "','" . $dateTo . "')" );	
		}
		elseif(strlen($dateFrom) > 0) {
			$query->addWhere(" r.date_receipt >= '" . $dateFrom . "'" );
		}
		elseif(strlen($dateTo) > 0) {
			$query->addWhere(" r.date_receipt <= '" . $dateTo . "'");
		}

		$db = DBCon::instance();
		// echo  $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		return $result;

	}

	
}

?>